Preview - Modeling U.S. Census Housing Data With Sinusoidal Functions

Activity 1: Linear Models and Sum of the Squared Error

Housing starts are the number of new residential construction projects that have begun during some period of time. The number of housing starts is considered to be a critical indicator of economic strength.

Table 1 shows the annual housing starts (in thousands) for single-family homes.  In this activity, you will enter the data into a spreadsheet along with an adjustable linear model.

 

Table 1: Annual Housing Starts (in thousands) for single-family homes 1990-1999

Year

‘90

‘91

‘92

‘93

‘94

‘95

‘96

‘97

‘98

‘99

Housing

895

840

1030

1126

1198

1076

1161

1134

1271

1302

 

 

Questions

Please answer the questions below.

Enter the data into your graphing calculator.  Determine a linear model for the data.  The linear model can be the linear regression determined by your calculator, or your own linear model.  Record your linear model here.

1. Open a spreadsheet using either Google Sheets or Excel. Create column headings exactly as shown below:

2.  Enter the information from Table 1 into your spreadsheet: Year (in Column A) and Housing Data (in Column B). Make sure to enter the first row of data in Row 2. 

3.  Enter the slope you calculated for your model into F2.  Enter the y-intercept of your model into G2.

4.  For the Year A2, Housing Starts B2, slope F2, and y-intercept G2, what is the formula for the linear model?  Write your answer below.

In both Google sheets and Excel, the formula for your model should be entered as:

=F2*A2+G2

Since we are using the same slope and y-intercept, we want to fix the values of F2 and G2, no matter where we use the formula. To do that, write the formula with a $ symbol for the row and column we want to stay fixed. Enter this formula in cell C2:

Enter the formula into cell D2 that you see here:

Describe in words what the squared error measures.

1.  Auto fill the columns for C and D by selecting both cells C2 and D2, then dragging down the “Autofill square” corner highlighted below:

The spreadsheet should automatically calculate the appropriate model values and the squared error for values in the other rows.

2.  Finally, we want to calculate the sum of the squared errors. At the bottom of Column D, type in the following sum command:

3.  Adjust the values for Slope and Intercept to minimize the sum of the squared errors.

Upload a screenshot of your spreadsheet or the file you created.

Upload files that are less than 5MB in size.
File Delete

Notes

These notes will appear on every page in this lesson so feel free to put anything here you'd like to keep track of.